﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.Drawing.Printing;
using System.IO;
using System.Threading;
using System.Windows.Forms;
using Class;
using MySql.Data.MySqlClient;

namespace Связь_с_БД
{
    public partial class FormMain : Form
    {
        public FormMain()
        {
            InitializeComponent();
            DictAll = new Dictionary<string, string>();
            DictLoad = new Dictionary<string, string>();
            dataTable = new DataTable();
            _bindsrc = new BindingSource();
            _numberRow = new List<int>();
            _countRowForPrint = 0;
            _id = 0;
        }

        #region Параметры для процедур
        private readonly string[] _paymentsParametres = {
                            "Id",
	                        "materialAssistanceHard",
	                        "materialAssistanceClothes",
	                        "socialGrant" };

        private readonly string[] _documentStudentParametres = {
                            "Id",
	                        "numberMatricula" ,
	                        "numberStudentCard" ,
	                        "numberPasport" ,
	                        "seriesPasport" ,
	                        "whoGivePasport" ,
	                        "xerocopyPasport",
	                        "xerocopyPolis",
	                        "certificate086",
	                        "photofluorogram",
	                        "vaccination" };

        private readonly string[] _privateDataParameters = {
                            "Id",
                            "fullName",
	                        "maidenName" ,
	                        "phoneHome" ,
	                        "phoneMobile",
	                        "children",
	                        "orphans",
	                        "marriage",
	                        "sex",
	                        "dateOfBirth",
	                        "interests",
	                        "address",
	                        "connect",
	                        "nationality1",
	                        "nationality2",
	                        "foreignLanguage",
                            "mailIndex"};

        private readonly string[] _previousEducationParametres = {
                            "Id",
	                        "yearFinishing",
	                        "education",
	                        "bachelorOrMaster",
	                        "previousInstitute",
	                        "diplomaAboutPE",
	                        "dataDiploma",
	                        "isRedDiploma",
	                        "seriesDiploma",
	                        "numberDiploma" };

        private readonly string[] _jobsParametres = {
                            "Id",
                            "helpers",
	                        "penalty",
	                        "helpFaculty",
	                        "steward",
	                        "secondSection",
	                        "notebook"};

        private readonly string[] _parentParametres = {
                            "Id",
                            "fioParents",
	                        "jobParents",
	                        "position",
	                        "phone"};

        private readonly string[] _learningParametres = {
                            "Id",
                            "scientificAdviser",
	                        "topicMasterDissertation",
	                        "achievements",
	                        "individualPlan",
	                        "term1",
	                        "term2",
	                        "term3",
	                        "publications",
                            "orderOn2ndCourse"};

        private readonly string[] _learningDataDocParametres = {
                            "Id",
                            "dateTakingDocument",
	                        "numberOrderOnTaking",
	                        "numberOrderOnExpulsion",
	                        "noteOnTaking",
	                        "noteOnExpulsion",
	                        "characterPractic",
	                        "placePractic",
	                        "markPractic" };

        private readonly string[] _learningDataParametres = {
                            "Id",
                            "numberSpeciality",
	                        "direction",
	                        "fullNameDirection",
	                        "codeProphyle",
	                        "financing",
	                        "typeLearning",
	                        "navigation",
	                        "factory",
	                        "department",
	                        "group_",
	                        "yearComing",
	                        "courseOfEducation",
	                        "yoa" };

        private readonly string[] _examsParametres = {
                            "Id",
                            "stateExam",
	                        "applicantOnRed",
	                        "markOnDiplom",
	                        "diplomMasterWithHonour",
	                        "numberAndDateDocumGAK",
	                        "chairmanGAK",
	                        "degree",
	                        "point" ,
	                        "orderOnLeave" };
        #endregion Parametres

        private string[] _documentStudentValue;
        private string[] _examsValue;
        private string[] _jobsValue;
        private string[] _learningDataValue;
        private string[] _learningValue;
        private string[] _learningDataDocumentValue;
        private string[] _parentsValue;
        private string[] _paymentsValue;
        private string[] _previousEducationValue;
        private string[] _privateDataValue;
        private int _id;
        private List<int> _numberRow;
        private int _countRowForPrint;
        private string _user;
        private string _password;
        private string _server;
        private string _nameDb;
        private BindingSource _bindsrc;
        private DataTable dataTable;
        protected Dictionary<string, string> DictAll;
        protected Dictionary<string, string> DictLoad;
        private PrintDataGridView _myDataGridViewPrinter;
        
        public void Form1Load(object sender, EventArgs e)
        {
            dataGridView1.AutoResizeColumns();
            dataGridView2.AutoResizeColumns();
            dataGridView1.AutoSize = true;
            dataGridView2.AutoSize = true;
            FillDictionaryLoad();
            FillDictionaryAll();
            LoginToolStripButton1Click(sender, e);
            //QueryAll();
            GetColumnsForView(DictLoad);
            ExchangeInDataGridHeader();
            AddItemsInComboBox();
        }

        public void SetUserId(string str)
        {
            _user = str;
        }
        public void SetPassword(string str)
        {
            _password = str;
        }
        public void SetNameBd(string str)
        {
            _nameDb = str;
        }
        public void SetServer(string str)
        {
            _server = str;
        }
        private string GetConnectionString()
        {
            return "Database=" + _nameDb + ";Data Source=" + _server + ";User Id=" + _user + ";Password=" + _password +
                   ";Allow Zero Datetime=true";
        }
        //импорт из 1997-2003 excel
        private void Excel199ToolStripMenuItemClick(object sender, EventArgs e)
        {
            var ofd = new OpenFileDialog { CheckPathExists = true, Multiselect = false, Filter = @"Файлы Excel 2003|*.xls" };

            try
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    var f = new Class1();
                    dataGridView2.DataSource = f.Open_2003(ofd.FileName);
                }
                Edit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            //Edit();
        }
        //импорт из 2007 excel
        private void Excel2007ToolStripMenuItemClick(object sender, EventArgs e)
        {
            var ofd = new OpenFileDialog { CheckPathExists = true, Multiselect = false, Filter = @"Файлы Excel 2007|*.xlsx" };

            try
            {
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    var f = new Class1();
                    dataGridView2.DataSource = f.Open_2007(ofd.FileName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        //запрос на все данные
        public void QueryAll()
        {
                const string sql = "SELECT * FROM StudentMain, LearnigData, PrivateData, DocumentStudent, " +
                                   "Payments, Jobs, LearningDataDoc, PreviousEducation, Parents, " +
                                   "Learning, Exams " +
                                   "WHERE " +
                                   "StudentMain.idLD = LearnigData.idLD AND " +
                                   "StudentMain.idPD = PrivateData.idPD AND " +
                                   "StudentMain.idDS = DocumentStudent.idDS AND " +
                                   "StudentMain.idPay = Payments.idPay AND " +
                                   "StudentMain.idJobs = Jobs.idJobs AND " +
                                   "StudentMain.idLDD = LearningDataDoc.idLDD AND " +
                                   "StudentMain.idPE = PreviousEducation.idPE AND " +
                                   "StudentMain.idPar = Parents.idPar AND " +
                                   "StudentMain.idLearn = Learning.idLearn AND " +
                                   "StudentMain.idExams = Exams.idExams ";
            string connect = GetConnectionString();
                using (var connection1 = new MySqlConnection(connect))
                {
                    try
                    {
                        connection1.Open();

                        var sqlDataAdapter1 = new MySqlDataAdapter();
                        sqlDataAdapter1.SelectCommand = new MySqlCommand(sql, connection1);
                        sqlDataAdapter1.Fill(dataTable);
                        
                    }
                    catch (MySqlException ex)
                    {
                        MessageBox.Show(ex.Message, @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                    finally
                    {
                        connection1.Close();
                        GC.Collect();
                    }
                    _bindsrc.DataSource = dataTable;
                    bindingNavigator1.BindingSource = _bindsrc;
                    dataGridView1.DataSource = _bindsrc;
                    dataGridView1.Show();
                }
        }
        //делает команды для добавления
        private MySqlCommand MakeCommandInsert(string[] values, string[] nameparametres)
        {
            var sqlcom = new MySqlCommand();
            for (int j = 0; j < values.Length; j++)
            {
                var sqlpar = new MySqlParameter();
                sqlpar.ParameterName = nameparametres[j + 1];
                sqlpar.Value = values[j];
                sqlcom.Parameters.Add(sqlpar);
            }
            return sqlcom;
        }
        //делает команды для обновления
        private MySqlCommand MakeCommandUpdate(int id, string[] values, string[] nameparametres)
        {
            var sqlcom = new MySqlCommand();
            for (int j = 0; j < nameparametres.Length; j++)
            {
                var sqlpar = new MySqlParameter();
                sqlpar.ParameterName = nameparametres[j];
                if (j == 0)
                {
                    sqlpar.Value = id.ToString();
                    sqlcom.Parameters.Add(sqlpar);
                    continue;
                }

                sqlpar.Value = values[j - 1];
                sqlcom.Parameters.Add(sqlpar);
            }
            return sqlcom;
        }
        //открывает форму добавления
        private void FormAddR(object sender, EventArgs e)
        {
            var formadd = new FormAddRow();
            formadd.Show(this);
        }
        //принимает данные с форм добавления или обновления и делает соотв. операции
        public void GetParametres(string[] docStudent,
                                   string[] exams,
                                   string[] jobs,
                                   string[] learningData,
                                   string[] learning,
                                   string[] learningDataDoc,
                                   string[] parent,
                                   string[] payments,
                                   string[] previousEduc,
                                   string[] praivateData,
                                   string command)
        {
            _documentStudentValue = docStudent;
            _examsValue = exams;
            _jobsValue = jobs;
            _learningDataValue = learningData;
            _learningValue = learning;
            _learningDataDocumentValue = learningDataDoc;
            _parentsValue = parent;
            _paymentsValue = payments;
            _previousEducationValue = previousEduc;
            _privateDataValue = praivateData;
            if (command == "Insert")
            {
                InsertEntryInBase();
            }
            if (command == "Update")
            {
                UpdateEntryInBase();
            }
        }
        //вызов хран процедур для добавления
        private void InsertEntryInBase()
        {
            string connectionString = GetConnectionString();
            var connection = new MySqlConnection(connectionString);
            try
            {
                connection.Open();

                MySqlCommand sqlcomm = new MySqlCommand("InsertDocumentStudent", connection);
                sqlcomm = MakeCommandInsert(_documentStudentValue, _documentStudentParametres);
                sqlcomm.CommandType = CommandType.StoredProcedure;
                sqlcomm.CommandText = "InsertDocumentStudent";

                MySqlCommand sqlcomm1 = connection.CreateCommand();
                sqlcomm1 = MakeCommandInsert(_examsValue, _examsParametres);
                sqlcomm1.CommandType = CommandType.StoredProcedure;
                sqlcomm1.CommandText = "InsertExams";

                MySqlCommand sqlcomm2 = connection.CreateCommand();
                sqlcomm2 = MakeCommandInsert(_jobsValue, _jobsParametres);
                sqlcomm2.CommandType = CommandType.StoredProcedure;
                sqlcomm2.CommandText = "InsertJobs";

                MySqlCommand sqlcomm3 = connection.CreateCommand();
                sqlcomm3 = MakeCommandInsert(_learningDataValue, _learningDataParametres);
                sqlcomm3.CommandType = CommandType.StoredProcedure;
                sqlcomm3.CommandText = "InsertLearnigData";

                MySqlCommand sqlcomm4 = connection.CreateCommand();
                sqlcomm4 = MakeCommandInsert(_learningValue, _learningParametres);
                sqlcomm4.CommandType = CommandType.StoredProcedure;
                sqlcomm4.CommandText = "InsertLearning";

                MySqlCommand sqlcomm5 = connection.CreateCommand();
                sqlcomm5 = MakeCommandInsert(_learningDataDocumentValue, _learningDataDocParametres);
                sqlcomm5.CommandType = CommandType.StoredProcedure;
                sqlcomm5.CommandText = "LearningDataDoc";

                MySqlCommand sqlcomm6 = connection.CreateCommand();
                sqlcomm6 = MakeCommandInsert(_parentsValue, _parentParametres);
                sqlcomm6.CommandType = CommandType.StoredProcedure;
                sqlcomm6.CommandText = "InsertParents";

                MySqlCommand sqlcomm7 = connection.CreateCommand();
                sqlcomm7 = MakeCommandInsert(_paymentsValue, _paymentsParametres);
                sqlcomm7.CommandType = CommandType.StoredProcedure;
                sqlcomm7.CommandText = "InsertPayments";

                MySqlCommand sqlcomm8 = connection.CreateCommand();
                sqlcomm8 = MakeCommandInsert(_previousEducationValue, _previousEducationParametres);
                sqlcomm8.CommandType = CommandType.StoredProcedure;
                sqlcomm8.CommandText = "InsertPreviousEducation";

                MySqlCommand sqlcomm9 = connection.CreateCommand();
                sqlcomm9 = MakeCommandInsert(_privateDataValue, _privateDataParameters);
                sqlcomm9.CommandType = CommandType.StoredProcedure;
                sqlcomm9.CommandText = "InsertPrivateData";

                MySqlCommand sqlcomm10 = connection.CreateCommand();
                var sqlpar = new MySqlParameter();
                sqlpar.ParameterName = "id";
                sqlpar.Value = "1";
                sqlcomm10.Parameters.Add(sqlpar);
                sqlcomm10.CommandType = CommandType.StoredProcedure;
                sqlcomm10.CommandText = "InsertStudentMain";


                sqlcomm.Connection = connection;
                sqlcomm.ExecuteScalar();

                sqlcomm1.Connection = connection;
                sqlcomm1.ExecuteScalar();

                sqlcomm2.Connection = connection;
                sqlcomm2.ExecuteScalar();

                sqlcomm3.Connection = connection;
                sqlcomm3.ExecuteScalar();

                sqlcomm4.Connection = connection;
                sqlcomm4.ExecuteScalar();

                sqlcomm5.Connection = connection;
                sqlcomm5.ExecuteScalar();

                sqlcomm6.Connection = connection;
                sqlcomm6.ExecuteScalar();

                sqlcomm7.Connection = connection;
                sqlcomm7.ExecuteScalar();

                sqlcomm8.Connection = connection;
                sqlcomm8.ExecuteScalar();

                sqlcomm9.Connection = connection;
                sqlcomm9.ExecuteScalar();

                sqlcomm10.Connection = connection;
                sqlcomm10.ExecuteScalar();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message, @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
                dataTable.Clear();
                QueryAll();
            }
        }
        //вызов хран процедур для изменения
        private void UpdateEntryInBase()
        {
            string connectionString = GetConnectionString();
            var connection = new MySqlConnection(connectionString);
            try
            {
                connection.Open();

                MySqlCommand sqlcomm = connection.CreateCommand();
                sqlcomm = MakeCommandUpdate(_id, _learningDataValue, _learningDataParametres);
                sqlcomm.CommandType = CommandType.StoredProcedure;
                sqlcomm.CommandText = "UpdateLearningData";

                MySqlCommand sqlcomm9 = connection.CreateCommand();
                sqlcomm9 = MakeCommandUpdate(_id, _privateDataValue, _privateDataParameters);
                sqlcomm9.CommandType = CommandType.StoredProcedure;
                sqlcomm9.CommandText = "UpdatePrivateData";

                MySqlCommand sqlcomm3 = connection.CreateCommand();
                sqlcomm3 = MakeCommandUpdate(_id, _documentStudentValue, _documentStudentParametres);
                sqlcomm3.CommandType = CommandType.StoredProcedure;
                sqlcomm3.CommandText = "UpdateDocumentStudent";

                MySqlCommand sqlcomm7 = connection.CreateCommand();
                sqlcomm7 = MakeCommandUpdate(_id, _paymentsValue, _paymentsParametres);
                sqlcomm7.CommandType = CommandType.StoredProcedure;
                sqlcomm7.CommandText = "UpdatePayments";

                MySqlCommand sqlcomm2 = connection.CreateCommand();
                sqlcomm2 = MakeCommandUpdate(_id, _jobsValue, _jobsParametres);
                sqlcomm2.CommandType = CommandType.StoredProcedure;
                sqlcomm2.CommandText = "UpdateJobs";

                MySqlCommand sqlcomm5 = connection.CreateCommand();
                sqlcomm5 = MakeCommandUpdate(_id, _learningDataDocumentValue, _learningDataDocParametres);
                sqlcomm5.CommandType = CommandType.StoredProcedure;
                sqlcomm5.CommandText = "UpdateLearningDataDoc";

                MySqlCommand sqlcomm8 = connection.CreateCommand();
                sqlcomm8 = MakeCommandUpdate(_id, _previousEducationValue, _previousEducationParametres);
                sqlcomm8.CommandType = CommandType.StoredProcedure;
                sqlcomm8.CommandText = "UpdatePreviousEducation";

                MySqlCommand sqlcomm6 = connection.CreateCommand();
                sqlcomm6 = MakeCommandUpdate(_id, _parentsValue, _parentParametres);
                sqlcomm6.CommandType = CommandType.StoredProcedure;
                sqlcomm6.CommandText = "UpdateParents";

                MySqlCommand sqlcomm4 = connection.CreateCommand();
                sqlcomm4 = MakeCommandUpdate(_id, _learningValue, _learningParametres);
                sqlcomm4.CommandType = CommandType.StoredProcedure;
                sqlcomm4.CommandText = "UpdateLearning";

                MySqlCommand sqlcomm1 = connection.CreateCommand();
                sqlcomm1 = MakeCommandUpdate(_id, _examsValue, _examsParametres);
                sqlcomm1.CommandType = CommandType.StoredProcedure;
                sqlcomm1.CommandText = "UpdateExams";

                sqlcomm.Connection = connection;
                sqlcomm.ExecuteScalar();

                sqlcomm1.Connection = connection;
                sqlcomm1.ExecuteScalar();

                sqlcomm2.Connection = connection;
                sqlcomm2.ExecuteScalar();

                sqlcomm3.Connection = connection;
                sqlcomm3.ExecuteScalar();

                sqlcomm4.Connection = connection;
                sqlcomm4.ExecuteScalar();

                sqlcomm5.Connection = connection;
                sqlcomm5.ExecuteScalar();

                sqlcomm6.Connection = connection;
                sqlcomm6.ExecuteScalar();

                sqlcomm7.Connection = connection;
                sqlcomm7.ExecuteScalar();

                sqlcomm8.Connection = connection;
                sqlcomm8.ExecuteScalar();

                sqlcomm9.Connection = connection;
                sqlcomm9.ExecuteScalar();

            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message, @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
                dataTable.Clear();
                QueryAll();
            }
        }
        //вызов хран процедур для удаления
        private void DeleteEntryInBase(int numberRow)
        {
            string connectionString = GetConnectionString();
            using (var connection = new MySqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    MySqlCommand sqlcomm = connection.CreateCommand();
                    var sqlpar = new MySqlParameter();
                    sqlpar.ParameterName = "idStud";
                    sqlpar.Value = numberRow;
                    sqlcomm.Parameters.Add(sqlpar);
                    sqlcomm.CommandType = CommandType.StoredProcedure;
                    sqlcomm.CommandText = "DeleteRow";


                    sqlcomm.Connection = connection;
                    sqlcomm.ExecuteScalar();
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show(ex.Message, @"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    connection.Close();
                    dataTable.Clear();
                    QueryAll();
                }
            }
        }
        //получает id  для обновления
        public void GetIdForUpdate(int id)
        {
            _id = id;
        }

        private void Edit()
        {
            // В этой функции удаляем ненужные столбцы

            string editsrt;

            dataGridView2.Columns.RemoveAt(0);

            //Editsrt = dataGridView2.Columns[0].Name;
            //dataGridView2.Columns.Remove(Editsrt);

            editsrt = dataGridView2.Columns[5].Name;
            dataGridView2.Columns.Remove(editsrt);

            editsrt = dataGridView2.Columns[6].Name;
            dataGridView2.Columns.Remove(editsrt);

            editsrt = dataGridView2.Columns[6].Name;
            dataGridView2.Columns.Remove(editsrt);

            editsrt = dataGridView2.Columns[7].Name;
            dataGridView2.Columns.Remove(editsrt);

            editsrt = dataGridView2.Columns[7].Name;
            dataGridView2.Columns.Remove(editsrt);

            editsrt = dataGridView2.Columns[8].Name;
            dataGridView2.Columns.Remove(editsrt);

            editsrt = dataGridView2.Columns[9].Name;
            dataGridView2.Columns.Remove(editsrt);

            editsrt = dataGridView2.Columns[10].Name;
            dataGridView2.Columns.Remove(editsrt);

            for (int j = 0; j < 16; j++)
            {
                editsrt = dataGridView2.Columns[10].Name;
                dataGridView2.Columns.Remove(editsrt);
                // dataGridView2.Columns.RemoveAt(10);
            }
        }

        public void SplitRowFromExcel()
        {
            int rowCount = 0;
            for (int i = 0; i < dataGridView2.Rows.Count;i++)
            {
                var isetb = new InfoStudentExcelToBase(dataGridView2.Rows[i]);
                isetb.SetAllData();
                _documentStudentValue = isetb.GetDocumentStudentValue();
                _examsValue = isetb.GetExamsValue();
                _jobsValue = isetb.GetJobsValue();
                _learningDataDocumentValue = isetb.GetLearningDataDocumentValue();
                _learningDataValue = isetb.GetLearningDataValue();
                _learningValue = isetb.GetLearningValue();
                _parentsValue = isetb.GetParentsValue();
                _paymentsValue = isetb.GetPaymentsValue();
                _previousEducationValue = isetb.GetPreviousEducationValue();
                _privateDataValue = isetb.GetPrivateDataValue();

                InsertEntryInBase();
                rowCount++;
            }
            MessageBox.Show(@"Добавлено " + rowCount + @" записей", @"Уведомление", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
        }
        //нажатипе на кнопу добавить в базу из excel
        private void Button3Click(object sender, EventArgs e)
        {
            SplitRowFromExcel();
        }
        //обновляем запись в базе
        private void UpdateRowClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                bool createdNew;
                using (Mutex mutex = new Mutex(true, "myApp", out createdNew))
                {
                    if (createdNew)
                    {
                        var formUpdate = new FormUpdateRow();
                        formUpdate.SetGridRow(dataGridView1.Rows[Convert.ToInt32(bindingNavigatorPositionItem.Text) - 1]);
                        formUpdate.Show(this);
                        formUpdate.GetOldValue();
                        formUpdate.SetOldValueOnForm();
                    }
                }
            }
        }
        //удаляем запись из базы
        private void DeleteEntryInBaseClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                if (MessageBox.Show(@"Вы уверены ?", @"Удаление", MessageBoxButtons.YesNo,
                                    MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    int numRow = Convert.ToInt32(bindingNavigatorPositionItem.Text) - 1;
                    DeleteEntryInBase(Convert.ToInt32(dataGridView1.Rows[numRow].Cells[1].Value));
                }
            }
        }
        //обновляемся с базы данных
        private void RefreshClick(object sender, EventArgs e)
        {
            dataTable.Clear();
            QueryAll();
        }
        //показываем контекстное меню при нажатии правой кнопки на gridview
        private void DataGridView1RowContextMenuStripNeeded(object sender, DataGridViewRowContextMenuStripNeededEventArgs e)
        {
            e.ContextMenuStrip = contextMenuStrip1;
            var rowNumber = e.RowIndex;
            dataGridView1.Rows[rowNumber].Selected = true;
            rowNumber++;
            bindingNavigatorPositionItem.Text = rowNumber.ToString();
            
        }
        //изменение записи
        private void ИзменитьToolStripMenuItemClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                var formUpdate = new FormUpdateRow();
                formUpdate.SetGridRow(dataGridView1.Rows[Convert.ToInt32(bindingNavigatorPositionItem.Text) - 1]);
                formUpdate.Show(this);
                formUpdate.GetOldValue();
                formUpdate.SetOldValueOnForm();
            }
        }
        //удалание записи 
        private void УдалитьЗаписьToolStripMenuItemClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                if (MessageBox.Show(@"Вы уверены ?", @"Удаление", MessageBoxButtons.YesNo,
                                    MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    var numRow = Convert.ToInt32(bindingNavigatorPositionItem.Text) - 1;
                    DeleteEntryInBase(Convert.ToInt32(dataGridView1.Rows[numRow].Cells[1].Value));
                }
            }
        }
        //печать общей справки 
        private void ОбщаяСправкаToolStripMenuItemClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                for (int i = 0; i < dataGridView1.Rows.Count;i++)
                {
                    if(dataGridView1.Rows[i].Cells[0].Value != null)
                        if ((bool)dataGridView1.Rows[i].Cells[0].Value)
                        {
                            var pd = new PrintDocumentMainCertificate(dataGridView1.Rows[i]);
                            pd.InsertInDocument();
                            int isOpenWord = 1;
                            while (isOpenWord == 1)
                            {
                                if (pd.IsClosed == false)
                                    Thread.Sleep(300);
                                else
                                    isOpenWord = 0;
                            }
                        }
                }
                
            }
        }
        //печать справки №9
        private void Справка9ToolStripMenuItemClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                for (int i = 0; i < dataGridView1.Rows.Count;i++)
                {
                    if(dataGridView1.Rows[i].Cells[0].Value != null)
                        if ((bool)dataGridView1.Rows[i].Cells[0].Value)
                        {
                            PrintDocumentCertificate9 pd = new PrintDocumentCertificate9(dataGridView1.Rows[i]);
                            pd.InsertInDocument();
                            int isOpenWord = 1;
                            while (isOpenWord == 1)
                            {
                                if (pd.IsClosed == false)
                                    Thread.Sleep(300);
                                else
                                    isOpenWord = 0;
                            }
                        }
                }
                
            }
        }
        //проверка на существование строки в gridview1
        private bool CheckNumberRow()
        {
            int curNumRow = Convert.ToInt32(bindingNavigatorPositionItem.Text);
            if (curNumRow < dataGridView1.Rows.Count) 
                return true;
            return false;
        }
        //заполнение словаря с названиями всех столбцов
        private void FillDictionaryAll()
        {
            var sr = File.OpenText(Application.StartupPath+@"\dictionary.txt");
            while(!sr.EndOfStream)
            {
                AddEntryFromFileLine(DictAll, sr.ReadLine());
            }
            sr.Close();
        }
        //заполнение словаря с данными о столбцах отображения
        private void FillDictionaryLoad()
        {
            var sr = File.OpenText(Application.StartupPath + @"\dictionaryLoad.txt");
            while (!sr.EndOfStream)
            {
                AddEntryFromFileLine(DictLoad, sr.ReadLine());
            }
            sr.Close();
        }
        //вспомогательный метод для заполнения словаря
        private void AddEntryFromFileLine(Dictionary<string,string> dict,string line)
        {
            string[] fields = line.Split('|');
            dict.Add(fields[0],fields[1]);
        }
        //переименовываем заголовки столбцов на русские эквиваленты
        private void ExchangeInDataGridHeader()
        {
            for(int i=0;i<dataGridView1.Columns.Count;i++)
            {
                
                    string tmp;
                    DictAll.TryGetValue(dataGridView1.Columns[i].HeaderText, out tmp);
                    dataGridView1.Columns[i].HeaderText = tmp;
                
            }
        }
        //добавление строк в combobox
        private void AddItemsInComboBox()
        {
            StreamReader sr = File.OpenText(Application.StartupPath + @"\dictionaryLoad.txt");
            while (!sr.EndOfStream)
            {
                string[] fields = sr.ReadLine().Split('|');
                comboBox1.Items.Add(fields[1]);
            }
            sr.Close();
        }
        //получение ключа по значению в словаре
        private string GetKeyByValue(Dictionary<string, string> d, string v)
        {
            foreach (string k in d.Keys)
            {
                if (d[k] == v) return k;
            }
            return string.Empty;
        }
        //поиск текста по указанной колонке
        private void SearchTextChanged(object sender, EventArgs e)
        {
            if (comboBox1.Text != string.Empty)
            {
                string text = textBox1.Text.ToLower();
                bool isFind = false;
                string column = GetKeyByValue(DictAll, comboBox1.Text);
                int number = dataGridView1.Columns[column].Index;
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    string text2 = dataGridView1.Rows[i].Cells[number].Value.ToString().ToLower();
                    if (text2.IndexOf(text) > -1)
                    {
                        isFind = true;
                    }
                }
                if (isFind)
                {
                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {
                        string text2 = dataGridView1.Rows[i].Cells[number].Value.ToString().ToLower();
                        if (text2.IndexOf(text) > -1)
                        {
                            dataGridView1.Rows[i].Visible = true;
                            dataGridView1.CurrentCell = dataGridView1.Rows[i].Cells[number];
                        }
                        else
                        {
                            dataGridView1.Rows[i].Visible = false;
                        }
                    }
                }
            }
        }
        //Оставляем нужные столбцы видимыми
        private void GetColumnsForView(Dictionary<string,string> dictionary)
        {
            for (var i = 1; i < dataGridView1.Columns.Count; i++)
                {
                    if (!dictionary.ContainsKey(dataGridView1.Columns[i].HeaderText))
                    {
                        try
                        {
                            dataGridView1.Columns[i].Visible = false;
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
        }
        //Оставляем нужные столбцы видимыми для настроек
        public void GetColumnsForView(string[] cols)
        {
            for (var i = 1; i < dataGridView1.Columns.Count; i++)
            {
                    try
                    {
                        dataGridView1.Columns[i].Visible = false;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                
            }
            comboBox1.Items.Clear();
            for(var i = 0; i < cols.Length; i++)
            {
                try
                {
                    if (IsHasDataGridHeaderText(cols[i]) > -1)
                    {
                        dataGridView1.Columns[IsHasDataGridHeaderText(cols[i])].Visible = true;
                        comboBox1.Items.Add(dataGridView1.Columns[IsHasDataGridHeaderText(cols[i])].HeaderText);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }
        //проверка на наличие нужного текста в хедере датагрида
        private int IsHasDataGridHeaderText(string str)
        {
            for(int i=0;i<dataGridView1.Columns.Count-1;i++)
            {
                if(dataGridView1.Columns[i].HeaderText == str)
                    return i;
            }
            return -1;
        }
        //Открыть окно о программе
        private void ОПрограммеToolStripMenuItem1Click(object sender, EventArgs e)
        {
            AboutForm form = new AboutForm();
            form.Show();
        }
        //Открыть форму добавления
        private void ToolStripMenuItem1Click(object sender, EventArgs e)
        {
            var formadd = new FormAddRow();
            formadd.Show(this);
        }
        //Открыть окно настроек
        private void НастроитьToolStripMenuItemClick(object sender, EventArgs e)
        {
            SettingsForm form = new SettingsForm();
            form.Show(this);
        }
        //Вернуть словарь со списком всех полей
        public Dictionary<string,string> GetDictionaryFull()
        {
            return DictAll;
        }
        //Вернуть словарь со списком полей,которые показываются при загрузке
        public Dictionary<string,string> GetDictionaryLoad()
        {
            return DictLoad;
        }
        //Открыть окно настроек
        private void SettingsClick(object sender, EventArgs e)
        {
            SettingsForm form = new SettingsForm();
            form.Show(this);
        }
        //Печать приказа на академ. отпуск
        public void PrintOrderAcademic(string[] str)
        {
            PrintDocumentOrderOnAcadem print = new PrintDocumentOrderOnAcadem(dataGridView1.Rows[_numberRow[_countRowForPrint-1]],str);
            print.InsertInDocument();
            int isOpenWord = 1;
            _countRowForPrint--;
            while (isOpenWord == 1)
            {
                if (print.IsClosed == false)
                    Thread.Sleep(300);
                else
                    isOpenWord = 0;
            }
            if(_countRowForPrint ==0)
                _numberRow.Clear();
        }
        //печать документа на отчисление
        public void PrintOrderOnOut(string[] str)
        {
            PrintDocumentOrderOut print = new PrintDocumentOrderOut(dataGridView1.Rows[_numberRow[_countRowForPrint-1]], str);
            print.InsertInDocument();
            int isOpenWord = 1;
            _countRowForPrint--;
            while (isOpenWord == 1)
            {
                if (print.IsClosed == false)
                    Thread.Sleep(300);
                else
                    isOpenWord = 0;
            }
            if (_countRowForPrint == 0)
                _numberRow.Clear();
        }
        //печать документа на перевод с программы на программу
        public void PrintOrderOnTransfer(string[] str)
        {
            PrintDocumentOrderOnTransfer print = new PrintDocumentOrderOnTransfer(dataGridView1.Rows[_numberRow[_countRowForPrint-1]], str);
            print.InsertInDocument();
            int isOpenWord = 1;
            _countRowForPrint--;
            while(isOpenWord == 1)
            {
                if(print.IsClosed == false)
                    Thread.Sleep(300);
                else
                    isOpenWord = 0;
            }
            if (_countRowForPrint == 0)
                 _numberRow.Clear();
        }
        //Нажатие на Печать на академ.отпуск
        private void НаАкадемическийОтпускToolStripMenuItemClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    if (dataGridView1.Rows[i].Cells[0].Value != null)
                        if ((bool) dataGridView1.Rows[i].Cells[0].Value)
                        {
                            _numberRow.Add(i);
                            _countRowForPrint++;
                            FormOrderAcadem form = new FormOrderAcadem();
                            form.Show(this);
                        }
                }
            }
        }
        //нажатие на печать документа на отчисление
        private void НаОтчислениеToolStripMenuItemClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    if (dataGridView1.Rows[i].Visible)
                    {
                        if (dataGridView1.Rows[i].Cells[0].Value != null)
                            if ((bool)dataGridView1.Rows[i].Cells[0].Value)
                            {
                                _numberRow.Add(i);
                                _countRowForPrint++;
                                FormOrderOut form = new FormOrderOut();
                                form.Show(this);
                            }
                    }
                }
            }
        }
        //занесение значений в checkbox в datagrid 
        private void DataGridView1CellClick(object sender, DataGridViewCellEventArgs e)
        {
            if(e.ColumnIndex == 0 && e.RowIndex>-1)
            {
                if (dataGridView1.Rows[e.RowIndex].Cells[0].Value == null)
                    dataGridView1.Rows[e.RowIndex].Cells[0].Value = true;   
                else
                    dataGridView1.Rows[e.RowIndex].Cells[0].Value = false;
            }
        }
        //клик по Login
        private void LoginToolStripButton1Click(object sender, EventArgs e)
        {
            FormLogin form = new FormLogin();
            form.ShowDialog(this);
        }
        //клик по Logout
        private void LogoutToolStripButton2Click(object sender, EventArgs e)
        {
            _password = string.Empty;
            _user = string.Empty;
            dataTable.Clear();
            QueryAll();
        }
        //нажатие на печать документа на перевод
        private void НаПереводToolStripMenuItemClick(object sender, EventArgs e)
        {
            if (CheckNumberRow())
            {
                for (int i = 0; i < dataGridView1.Rows.Count; i++)
                {
                    if (dataGridView1.Rows[i].Cells[0].Value != null)
                        if ((bool)dataGridView1.Rows[i].Cells[0].Value)
                        {
                            _numberRow.Add(i);
                            _countRowForPrint++;
                            FormOrderOnTransfer form = new FormOrderOnTransfer();
                            form.Show(this);
                        }
                }
            }
        }
        //нажатие на хэдере колоник с checkbox-ми
        private void DataGridView1ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if(e.ColumnIndex == 0)
            {
                for (int i = 0; i < dataGridView1.Rows.Count;i++ )
                    if (dataGridView1.Rows[i].Visible)
                    {
                        if (dataGridView1.Rows[i].Cells[0].Value == null ||
                            (bool) dataGridView1.Rows[i].Cells[0].Value == false)
                            dataGridView1.Rows[i].Cells[0].Value = true;
                        else
                            dataGridView1.Rows[i].Cells[0].Value = false;
                    }
            }
        }

        private bool SetupThePrinting()
        {
            PrintDialog MyPrintDialog = new PrintDialog();
            MyPrintDialog.AllowCurrentPage = false;
            MyPrintDialog.AllowPrintToFile = false;
            MyPrintDialog.AllowSelection = false;
            MyPrintDialog.AllowSomePages = false;
            MyPrintDialog.PrintToFile = false;
            MyPrintDialog.ShowHelp = false;
            MyPrintDialog.ShowNetwork = false;

            if (MyPrintDialog.ShowDialog() != DialogResult.OK)
                return false;

            MyPrintDocument.DocumentName = "ACДеканат";
            MyPrintDocument.PrinterSettings =
                                MyPrintDialog.PrinterSettings;
            MyPrintDocument.DefaultPageSettings =
            MyPrintDialog.PrinterSettings.DefaultPageSettings;
            MyPrintDocument.DefaultPageSettings.Margins =
                             new Margins(40, 40, 40, 40);

            //if (MessageBox.Show(@"Do you want the report to be centered on the page",
            //    @"InvoiceManager - Center on Page", MessageBoxButtons.YesNo,
            //    MessageBoxIcon.Question) == DialogResult.Yes)
                _myDataGridViewPrinter = new PrintDataGridView(dataGridView1,
                MyPrintDocument, true, true, " ", new Font("Tahoma", 18,
                FontStyle.Bold, GraphicsUnit.Point), Color.Black, true);
            //else
            //    MyDataGridViewPrinter = new PrintDataGridView(dataGridView1,
            //    MyPrintDocument, false, true, " ", new Font("Tahoma", 18,
            //    FontStyle.Bold, GraphicsUnit.Point), Color.Black, true);

            return true;
        }

        private void MyPrintDocumentPrintPage(object sender, PrintPageEventArgs e)
        {
            bool more = _myDataGridViewPrinter.DrawDataGridView(e.Graphics);
            if (more)
                e.HasMorePages = true;
        }

        private void ToolStripButtonPrintClick(object sender, EventArgs e)
        {
            if (SetupThePrinting())
                MyPrintDocument.Print();
        }

        private void ToolStripButtonPrintPreviewClick(object sender, EventArgs e)
        {
            if (SetupThePrinting())
            {
                PrintPreviewDialog MyPrintPreviewDialog = new PrintPreviewDialog();
                MyPrintPreviewDialog.Document = MyPrintDocument;
                MyPrintPreviewDialog.ShowDialog();
            }
        }

        private void MyPrintDocumentBeginPrint(object sender, PrintEventArgs e)
        {
            dataGridView1.Columns[0].Visible = false;
        }

        private void MyPrintDocumentEndPrint(object sender, PrintEventArgs e)
        {
            dataGridView1.Columns[0].Visible = true;
        }
    }
}
